﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ArticleDBConnection$" formatter="bizobjlist of Article(author,primaryPicture)">
	<FormatterInfo ArticleFieldType="columnName">
		<code for="postEach" on="Article">
			<![CDATA[
			try
			{
				if(CurrentBizObj.get("mediaCacheName").ToString().Length==0)
					CurrentBizObj.put("mediaCacheName",info.get("defaultCacheName"));
			}
			catch(Exception e)
			{
				
			}
			 
			try
			{
				string store = Cmn.pathCombine(App.resMgr.getMacro("BaseArticleStore").ToString()
							,App.resMgr.resolve("$CmpSetting(sitePath,force)$"));		
				if((DetailLevel)info.get("details") > DetailLevel.basicDetails)
				{
					string body = CurrentBizObj.get("baseBody").ToString();
					IBizObjSource src = null;		 
					if(body.StartsWith("_scs_") || (body.Length > 4 && ".txt".Equals(body.Substring(body.Length-4))))
					{
						string filePath = System.IO.Path.Combine(store,body);
						CurrentBizObj.put("baseBody",System.IO.File.ReadAllText(filePath));
					}
					IBizObjList list = CurrentBizObj.get("translatedBodies") as IBizObjList;
					if(list.numElements > 0)
					{
						string v = System.IO.Path.Combine(store,list[0].get("v").ToString());
						if(v.StartsWith("_scs_") || (v.Length > 4 && ".txt".Equals(v.Substring(v.Length-4))))
							CurrentBizObj.put("translatedBodies",App.bizObjMgr.createList(SourceType.BasicSource,System.IO.File.ReadAllText(v),"ArticleLT",null));
					}
				}
			}catch(Exception e)
			{
				
			}
			
		]]></code>
	</FormatterInfo>
	<Sql>
		<code>DetailLevel details = (DetailLevel)info.get("details");</code>
		SELECT
			a.id,
			<code><![CDATA[
				if(details > DetailLevel.idOnly)
					sql.Append("a.baseTitle,a.uniqueTitle,");
				if(details > DetailLevel.idAndTitle)
				{
					sql.Append(@"
						a.summary AS baseSummary, a.keyword, a.dateUpdated, a.dateCreated,
						u.lastName AS authorlastName, u.id AS authorid, u.firstName AS authorfirstName, u.userName AS authorusername, 
						p.id AS primaryPictureId,
						p.name AS primaryPictureName, p.fileName AS primaryPictureFileName, p.caption AS primaryPictureCaption, 
					");
					if(true.Equals(info.get("includeTranslations"))) 
						sql.Append("a.translatedTitles,a.translatedSummaries,a.translationDetails,");
				}
				if(details > DetailLevel.basicDetails)
				{
					sql.Append("a.body as baseBody,");
					if(true.Equals(info.get("includeTranslations")))
						sql.Append("a.translatedBodies,");
				}
			]]></code>
			t.objectId, t.parentId, t.parentHostId, c.keyName, c.depth, t.rootId
		FROM            
			Articles AS a 
			LEFT OUTER JOIN	ObjectTreeNodes AS t ON a.id = t.objectId
			LEFT OUTER JOIN ObjectTreeCounts as c on c.objectId = t.objectId
			<code><![CDATA[
			if(details > DetailLevel.idAndTitle)
				sql.Append(@"
					LEFT OUTER JOIN	Users AS u ON u.id = a.authorId 
					LEFT OUTER JOIN	Pictures AS p ON p.id = a.id 
				");
			]]></code>
		WHERE
			<code><![CDATA[
			if(true.Equals(info.get("entireTree")))
				sql.Append("t.parentHostId = @articleId ");
			else
				sql.Append("t.parentId = @articleId ");
			]]></code>
		     AND
			<code><![CDATA[
				int startDepth = (int)info.get("startDepth");
				int endDepth = (int)info.get("endDepth");
				if(startDepth != -1 && startDepth == endDepth)
					sql.Append("c.depth = ").Append(startDepth).Append(" AND ");
				else if(startDepth != -1 && endDepth != -1)
					sql.AppendFormat("c.depth >= {0} AND c.depth <= {1} AND ", startDepth, endDepth);
			]]></code>
			a.companyId = @companyId 
		ORDER BY 
			c.keyName, t.id
			
	</Sql>
	<Param name="uniqueName" type="UnicodeVarChar" />
	<Param name="companyId" type="UniqueIdentifier" />
</AdoNetDataAccess>
